// 用户相关的SQL语句
module.exports = {
  // 登录查询
  LOGIN_QUERY: `
    SELECT u.*, r.role_name 
    FROM users u 
    LEFT JOIN user_roles r ON u.role_id = r.id 
    WHERE u.username = ? AND u.status = 'active'
  `,

  // 更新最后登录时间
  UPDATE_LAST_LOGIN: `
    UPDATE users 
    SET last_login = ? 
    WHERE id = ?
  `,

  // 通过ID查询用户
  GET_USER_BY_ID: `
    SELECT u.*, r.role_name 
    FROM users u 
    LEFT JOIN user_roles r ON u.role_id = r.id 
    WHERE u.id = ?
  `,

  // 创建新用户
  CREATE_USER: `
    INSERT INTO users (
      username, password, role_id, real_name, 
      phone, email, gender, birth_date, status
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
  `,

  // 检查用户名是否存在
  CHECK_USERNAME: `
    SELECT id FROM users WHERE username = ?
  `,

  // 创建新用户（包含更多字段）
  REGISTER_USER: `
    INSERT INTO users (
      username, password, role_id, real_name, 
      phone, email, gender, birth_date, status
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'active')
  `,

  // 获取角色ID
  GET_ROLE_ID: `
    SELECT id FROM user_roles WHERE role_name = ?
  `,

  // 获取用户详细信息
  GET_USER_INFO: `
    SELECT 
      u.username,
      u.real_name,
      u.phone,
      u.email,
      u.gender,
      u.birth_date,
      u.status,
      u.avatar_url,
      r.role_name,
      CASE 
        WHEN r.role_name = 'student' THEN (
          SELECT JSON_OBJECT(
            'emergency_contact', s.emergency_contact,
            'emergency_phone', s.emergency_phone,
            'dance_level', s.dance_level,
            'health_note', s.health_note
          )
          FROM student_profiles s
          WHERE s.user_id = u.id
        )
        WHEN r.role_name = 'teacher' THEN (
          SELECT JSON_OBJECT(
            'specialties', t.specialties,
            'experience_years', t.experience_years,
            'certification', t.certification,
            'introduction', t.introduction,
            'achievement', t.achievement
          )
          FROM teacher_profiles t
          WHERE t.user_id = u.id
        )
        ELSE NULL
      END as profile_info
    FROM users u
    LEFT JOIN user_roles r ON u.role_id = r.id
    WHERE u.id = ? AND u.status = 'active'
  `,

  // 更新用户角色
  UPDATE_USER_ROLE: `
    UPDATE users 
    SET role_id = (SELECT id FROM user_roles WHERE role_name = ?)
    WHERE id = ?
  `
}; 